USE [Test_Events]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetEventIndicatorsByCompanyIds2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds2]
GO


CREATE PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds2]
	@CompanyIds NVARCHAR(MAX), @timestamp DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT	EventIndicators.Id AS EventIndicatorId,
            BaseEventIndicators.Name AS EventIndicatorName, 
			BaseEventIndicators.SortKey AS EventIndicatorSortKey, 
			BaseEventIndicators.Id AS BaseEventIndicatorId,
			EventIndicatorCompanyProfiles.CompanyId, 
			BaseEventIndicators.ControversyIndicatorId,
			ControversyIndicators.Name AS ControversyIndicatorName,
			ControversyIndicators.Number AS ControversyIndicatorNumber,
			ControversyIndicators.SortKey AS ControversyIndicatorSortKey,
			EventIndicators.ProvidesControversy,
			IIF(AnswerCategories.Id > 2, EventIndicators.ControversySummary_Value, '') AS ControversySummary,
            AnswerCategories.Id AS AnswerCategoryId,
			AnswerCategories.Text AS AnswerCategoryText, 
			AnswerCategories.Score AS AnswerCategoryScore,
			EventIndicators.ACJustification_Value AS AnswerCategoryJustification,
            EventIndicators.Impact_Value AS ImpactScore, 
			EventIndicators.ImpactText_Value AS ImpactText,
			EventIndicators.ImpactCommentsId,

            EventIndicators.Risk_Value AS RiskScore, 
			EventIndicators.RiskText_Value AS RiskText,
			EventIndicators.RiskCommentsId,

            EventIndicators.ManagementAC_Value AS PreparednessScore, 
			EventIndicators.ManagementText_Value AS PreparednessText,
			EventIndicators.ManagementCommentsId AS PreparednessCommentsId,
             
			EventIndicators.OutlookAC_Value AS OutlookScore, 
			EventIndicators.OutlookText_Value AS OutlookText, 
			EventIndicators.OutlookCommentsId,
			@timestamp AS Timestamp

	FROM	dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
    INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicatorCompanyProfiles.Id = EventIndicators.CompanyProfileId 
    INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id 
    INNER JOIN dbo.ControversyIndicators AS ControversyIndicators ON BaseEventIndicators.ControversyIndicatorId = ControversyIndicators.Id 
    INNER JOIN dbo.AnswerCategoryProperties AS AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id 
	INNER JOIN dbo.AnswerCategories AS AnswerCategories ON AnswerCategoryProperties.ValueId = AnswerCategories.Id
	WHERE  (EventIndicatorCompanyProfiles.Status = 0 and BaseEventIndicators.UnavailableToClients = 0)
	ORDER BY EventIndicatorSortKey
END
GO


